<!DOCTYPE html>
<html lang="en-US">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>多表查询 | 知识库</title>
    <meta name="generator" content="VuePress 1.9.9">
    <link rel="icon" href="/noteslibrary/img/favicon.ico">
    <meta name="description" content="Java后端技术博客,专注Java后端学习与总结。HTML,CSS,JavaScript,TypeScript,JavaSE,Python,Vue,React,Node,Git,Github等技术文章。">
    <meta name="keywords" content="后端博客,个人技术博客,后端,后端开发,后端框架,后端面试题,技术文档,学习,面试,HTML,CSS,JavaScript,TypeScript,JavaSE,Python,Vue,React,Node,Git,Github">
    <meta name="theme-color" content="#11a8cd">
    
    <link rel="preload" href="/noteslibrary/assets/css/0.styles.2500ff6d.css" as="style"><link rel="preload" href="/noteslibrary/assets/js/app.dc0c3d24.js" as="script"><link rel="preload" href="/noteslibrary/assets/js/2.a664539c.js" as="script"><link rel="preload" href="/noteslibrary/assets/js/93.bbc81ca6.js" as="script"><link rel="prefetch" href="/noteslibrary/assets/js/10.bab1a8b9.js"><link rel="prefetch" href="/noteslibrary/assets/js/100.245d2d1d.js"><link rel="prefetch" href="/noteslibrary/assets/js/101.9d1c6c13.js"><link rel="prefetch" href="/noteslibrary/assets/js/102.d187686f.js"><link rel="prefetch" href="/noteslibrary/assets/js/103.b0e7acf6.js"><link rel="prefetch" href="/noteslibrary/assets/js/104.895c9e53.js"><link rel="prefetch" href="/noteslibrary/assets/js/105.235036be.js"><link rel="prefetch" href="/noteslibrary/assets/js/106.17a71ae1.js"><link rel="prefetch" href="/noteslibrary/assets/js/107.256ceb5d.js"><link rel="prefetch" href="/noteslibrary/assets/js/108.57832d85.js"><link rel="prefetch" href="/noteslibrary/assets/js/109.f8b1d1c7.js"><link rel="prefetch" href="/noteslibrary/assets/js/11.2c69801a.js"><link rel="prefetch" href="/noteslibrary/assets/js/110.f3ce6a68.js"><link rel="prefetch" href="/noteslibrary/assets/js/111.bdfd28f4.js"><link rel="prefetch" href="/noteslibrary/assets/js/112.878f599c.js"><link rel="prefetch" href="/noteslibrary/assets/js/113.0efa07cf.js"><link rel="prefetch" href="/noteslibrary/assets/js/114.c7aba251.js"><link rel="prefetch" href="/noteslibrary/assets/js/115.8b2e628d.js"><link rel="prefetch" href="/noteslibrary/assets/js/116.3392d8ba.js"><link rel="prefetch" href="/noteslibrary/assets/js/117.310538d0.js"><link rel="prefetch" href="/noteslibrary/assets/js/118.81eb015b.js"><link rel="prefetch" href="/noteslibrary/assets/js/12.e77f5a18.js"><link rel="prefetch" href="/noteslibrary/assets/js/13.d24d0054.js"><link rel="prefetch" href="/noteslibrary/assets/js/14.f29d4d33.js"><link rel="prefetch" href="/noteslibrary/assets/js/15.8694e081.js"><link rel="prefetch" href="/noteslibrary/assets/js/16.03ffb143.js"><link rel="prefetch" href="/noteslibrary/assets/js/17.83edd7b9.js"><link rel="prefetch" href="/noteslibrary/assets/js/18.58ca4447.js"><link rel="prefetch" href="/noteslibrary/assets/js/19.e136d5c2.js"><link rel="prefetch" href="/noteslibrary/assets/js/20.fd3961b6.js"><link rel="prefetch" href="/noteslibrary/assets/js/21.207406c6.js"><link rel="prefetch" href="/noteslibrary/assets/js/22.b8738ce2.js"><link rel="prefetch" href="/noteslibrary/assets/js/23.33e4529d.js"><link rel="prefetch" href="/noteslibrary/assets/js/24.ab5493c5.js"><link rel="prefetch" href="/noteslibrary/assets/js/25.2506ce48.js"><link rel="prefetch" href="/noteslibrary/assets/js/26.7e6a9c14.js"><link rel="prefetch" href="/noteslibrary/assets/js/27.e7b4e92a.js"><link rel="prefetch" href="/noteslibrary/assets/js/28.7ad46ba6.js"><link rel="prefetch" href="/noteslibrary/assets/js/29.81666f41.js"><link rel="prefetch" href="/noteslibrary/assets/js/3.8f13cd17.js"><link rel="prefetch" href="/noteslibrary/assets/js/30.07ada09b.js"><link rel="prefetch" href="/noteslibrary/assets/js/31.f271c8cf.js"><link rel="prefetch" href="/noteslibrary/assets/js/32.2350914c.js"><link rel="prefetch" href="/noteslibrary/assets/js/33.9d7bfb77.js"><link rel="prefetch" href="/noteslibrary/assets/js/34.6fcf6f6b.js"><link rel="prefetch" href="/noteslibrary/assets/js/35.0c3a88fb.js"><link rel="prefetch" href="/noteslibrary/assets/js/36.574ca92a.js"><link rel="prefetch" href="/noteslibrary/assets/js/37.72e38074.js"><link rel="prefetch" href="/noteslibrary/assets/js/38.16d408fb.js"><link rel="prefetch" href="/noteslibrary/assets/js/39.63abc4a8.js"><link rel="prefetch" href="/noteslibrary/assets/js/4.3e68fc94.js"><link rel="prefetch" href="/noteslibrary/assets/js/40.ca2bdd48.js"><link rel="prefetch" href="/noteslibrary/assets/js/41.f82c80ec.js"><link rel="prefetch" href="/noteslibrary/assets/js/42.d1b8f579.js"><link rel="prefetch" href="/noteslibrary/assets/js/43.d3fd6260.js"><link rel="prefetch" href="/noteslibrary/assets/js/44.491f9afa.js"><link rel="prefetch" href="/noteslibrary/assets/js/45.1ee0d084.js"><link rel="prefetch" href="/noteslibrary/assets/js/46.a3e0be1f.js"><link rel="prefetch" href="/noteslibrary/assets/js/47.647908d1.js"><link rel="prefetch" href="/noteslibrary/assets/js/48.9658b8b0.js"><link rel="prefetch" href="/noteslibrary/assets/js/49.8f4b8327.js"><link rel="prefetch" href="/noteslibrary/assets/js/5.a0a97ccd.js"><link rel="prefetch" href="/noteslibrary/assets/js/50.f46d1433.js"><link rel="prefetch" href="/noteslibrary/assets/js/51.f1b784a0.js"><link rel="prefetch" href="/noteslibrary/assets/js/52.cce45956.js"><link rel="prefetch" href="/noteslibrary/assets/js/53.d66dabe3.js"><link rel="prefetch" href="/noteslibrary/assets/js/54.384b864b.js"><link rel="prefetch" href="/noteslibrary/assets/js/55.47ed19f2.js"><link rel="prefetch" href="/noteslibrary/assets/js/56.193cd456.js"><link rel="prefetch" href="/noteslibrary/assets/js/57.e6ea1f8c.js"><link rel="prefetch" href="/noteslibrary/assets/js/58.97fd2330.js"><link rel="prefetch" href="/noteslibrary/assets/js/59.b0c3d9ea.js"><link rel="prefetch" href="/noteslibrary/assets/js/6.50cbd75f.js"><link rel="prefetch" href="/noteslibrary/assets/js/60.d01d0651.js"><link rel="prefetch" href="/noteslibrary/assets/js/61.385e9bae.js"><link rel="prefetch" href="/noteslibrary/assets/js/62.a93fa4c8.js"><link rel="prefetch" href="/noteslibrary/assets/js/63.f72a2142.js"><link rel="prefetch" href="/noteslibrary/assets/js/64.3bf0b024.js"><link rel="prefetch" href="/noteslibrary/assets/js/65.cb1cb3bb.js"><link rel="prefetch" href="/noteslibrary/assets/js/66.4c9ff8cd.js"><link rel="prefetch" href="/noteslibrary/assets/js/67.2fc17900.js"><link rel="prefetch" href="/noteslibrary/assets/js/68.fd3ee410.js"><link rel="prefetch" href="/noteslibrary/assets/js/69.682be05d.js"><link rel="prefetch" href="/noteslibrary/assets/js/7.80203dee.js"><link rel="prefetch" href="/noteslibrary/assets/js/70.29428a45.js"><link rel="prefetch" href="/noteslibrary/assets/js/71.aff6ef6b.js"><link rel="prefetch" href="/noteslibrary/assets/js/72.fe7572e0.js"><link rel="prefetch" href="/noteslibrary/assets/js/73.e52bc1a7.js"><link rel="prefetch" href="/noteslibrary/assets/js/74.5e72ee84.js"><link rel="prefetch" href="/noteslibrary/assets/js/75.5a5bba64.js"><link rel="prefetch" href="/noteslibrary/assets/js/76.70d6ff7c.js"><link rel="prefetch" href="/noteslibrary/assets/js/77.1db1c302.js"><link rel="prefetch" href="/noteslibrary/assets/js/78.137c92e8.js"><link rel="prefetch" href="/noteslibrary/assets/js/79.8455d34b.js"><link rel="prefetch" href="/noteslibrary/assets/js/8.9e13e493.js"><link rel="prefetch" href="/noteslibrary/assets/js/80.127f3a4e.js"><link rel="prefetch" href="/noteslibrary/assets/js/81.191d3614.js"><link rel="prefetch" href="/noteslibrary/assets/js/82.6ae31745.js"><link rel="prefetch" href="/noteslibrary/assets/js/83.640cd3d7.js"><link rel="prefetch" href="/noteslibrary/assets/js/84.22c036e2.js"><link rel="prefetch" href="/noteslibrary/assets/js/85.692cd496.js"><link rel="prefetch" href="/noteslibrary/assets/js/86.793e38a7.js"><link rel="prefetch" href="/noteslibrary/assets/js/87.eb27f1d6.js"><link rel="prefetch" href="/noteslibrary/assets/js/88.6d48c75e.js"><link rel="prefetch" href="/noteslibrary/assets/js/89.a798bacb.js"><link rel="prefetch" href="/noteslibrary/assets/js/9.7e864ff7.js"><link rel="prefetch" href="/noteslibrary/assets/js/90.e814aadc.js"><link rel="prefetch" href="/noteslibrary/assets/js/91.5c132772.js"><link rel="prefetch" href="/noteslibrary/assets/js/92.d0d1984d.js"><link rel="prefetch" href="/noteslibrary/assets/js/94.242fbc29.js"><link rel="prefetch" href="/noteslibrary/assets/js/95.f7232d68.js"><link rel="prefetch" href="/noteslibrary/assets/js/96.440e6bbe.js"><link rel="prefetch" href="/noteslibrary/assets/js/97.a8ad3aa8.js"><link rel="prefetch" href="/noteslibrary/assets/js/98.e9a01f98.js"><link rel="prefetch" href="/noteslibrary/assets/js/99.e024f354.js">
    <link rel="stylesheet" href="/noteslibrary/assets/css/0.styles.2500ff6d.css">
  </head>
  <body class="theme-mode-light">
    <div id="app" data-server-rendered="true"><div class="theme-container sidebar-open have-rightmenu"><header class="navbar blur"><div title="目录" class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/noteslibrary/" class="home-link router-link-active"><img src="/noteslibrary/img/EB-logo.png" alt="知识库" class="logo"> <span class="site-name can-hide">知识库</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/noteslibrary/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="基础" class="dropdown-title"><a href="/noteslibrary/basis/" class="link-title">基础</a> <span class="title" style="display:none;">基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机网络》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机组成原理》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《操作系统》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《数据结构》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《算法》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《设计模式》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/noteslibrary/fontend/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/0796ba76b4b55368.html" class="nav-link">《JS笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f344d070a1031ef7.html" class="nav-link">《ES6笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/note/vue/" class="nav-link">《Vue笔记》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/noteslibrary/backend/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>JavaEE框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/d0b17d/" class="nav-link">《DAO全家桶》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a9ac80/" class="nav-link">《Spring全家桶》</a></li></ul></li><li class="dropdown-item"><h4>BigData框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3a50a8/404" class="nav-link">《Hadoop生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/3a50a8/404" class="nav-link">《Spark生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/3a50a8/404" class="nav-link">《Flink生态》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><a href="/noteslibrary/database/" class="link-title">数据库</a> <span class="title" style="display:none;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/32e9fd/" class="nav-link">MySQL</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="工具" class="dropdown-title"><a href="/noteslibrary/tools/" class="link-title">工具</a> <span class="title" style="display:none;">工具</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac065/" class="nav-link">Maven</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/5760de/" class="nav-link">Git</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="收藏" class="dropdown-title"><a href="/noteslibrary/collect/" class="link-title">收藏</a> <span class="title" style="display:none;">收藏</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/50c072/" class="nav-link">博客收藏</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其他" class="dropdown-title"><a href="/noteslibrary/others/" class="link-title">其他</a> <span class="title" style="display:none;">其他</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客部署</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/db737d/" class="nav-link">Hexo</a></li></ul></li><li class="dropdown-item"><h4>系统安装</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac066/" class="nav-link">系统安装</a></li></ul></li><li class="dropdown-item"><h4>idea快捷键</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/111c73/" class="nav-link">idea快捷键</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/noteslibrary/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/noteslibrary/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/lcfqzd/vuepress-theme-vdoing" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask"></div> <div class="sidebar-hover-trigger"></div> <aside class="sidebar" style="display:none;"><!----> <nav class="nav-links"><div class="nav-item"><a href="/noteslibrary/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="基础" class="dropdown-title"><a href="/noteslibrary/basis/" class="link-title">基础</a> <span class="title" style="display:none;">基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机网络》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机组成原理》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《操作系统》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《数据结构》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《算法》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《设计模式》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/noteslibrary/fontend/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/0796ba76b4b55368.html" class="nav-link">《JS笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f344d070a1031ef7.html" class="nav-link">《ES6笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/note/vue/" class="nav-link">《Vue笔记》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/noteslibrary/backend/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>JavaEE框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/d0b17d/" class="nav-link">《DAO全家桶》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a9ac80/" class="nav-link">《Spring全家桶》</a></li></ul></li><li class="dropdown-item"><h4>BigData框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3a50a8/404" class="nav-link">《Hadoop生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/3a50a8/404" class="nav-link">《Spark生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/3a50a8/404" class="nav-link">《Flink生态》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><a href="/noteslibrary/database/" class="link-title">数据库</a> <span class="title" style="display:none;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/32e9fd/" class="nav-link">MySQL</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="工具" class="dropdown-title"><a href="/noteslibrary/tools/" class="link-title">工具</a> <span class="title" style="display:none;">工具</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac065/" class="nav-link">Maven</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/5760de/" class="nav-link">Git</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="收藏" class="dropdown-title"><a href="/noteslibrary/collect/" class="link-title">收藏</a> <span class="title" style="display:none;">收藏</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/50c072/" class="nav-link">博客收藏</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其他" class="dropdown-title"><a href="/noteslibrary/others/" class="link-title">其他</a> <span class="title" style="display:none;">其他</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客部署</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/db737d/" class="nav-link">Hexo</a></li></ul></li><li class="dropdown-item"><h4>系统安装</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac066/" class="nav-link">系统安装</a></li></ul></li><li class="dropdown-item"><h4>idea快捷键</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/111c73/" class="nav-link">idea快捷键</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/noteslibrary/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/noteslibrary/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/lcfqzd/vuepress-theme-vdoing" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>MySQL笔记</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/noteslibrary/pages/32e9fd/" class="sidebar-link">写在前面</a></li><li><a href="/noteslibrary/pages/cfda94/" class="sidebar-link">数据库概述</a></li><li><a href="/noteslibrary/pages/497bcb/" class="sidebar-link">MySQL环境搭建</a></li><li><a href="/noteslibrary/pages/9c9f3a/" class="sidebar-link">基本的SELECT语句</a></li><li><a href="/noteslibrary/pages/b59830/" class="sidebar-link">运算符</a></li><li><a href="/noteslibrary/pages/6fef5b/" class="sidebar-link">排序与分页</a></li><li><a href="/noteslibrary/pages/3a50a8/" aria-current="page" class="active sidebar-link">多表查询</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/3a50a8/#_1-一个案例引发的多表连接" class="sidebar-link">1. 一个案例引发的多表连接</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3a50a8/#_1-1-案例说明" class="sidebar-link">1.1 案例说明</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3a50a8/#_1-2-笛卡尔积-或交叉连接-的理解" class="sidebar-link">1.2 笛卡尔积（或交叉连接）的理解</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3a50a8/#_1-3-案例分析与问题解决" class="sidebar-link">1.3 案例分析与问题解决</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/3a50a8/#_2-多表查询分类讲解" class="sidebar-link">2. 多表查询分类讲解</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3a50a8/#_2-1、分类1-等值连接vs非等值连接" class="sidebar-link">2.1、分类1：等值连接VS非等值连接</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3a50a8/#_2-2、分类2-自连接-vs-非自连接" class="sidebar-link">2.2、分类2：自连接 vs 非自连接</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3a50a8/#_2-3、分类3-内连接-vs-外连接" class="sidebar-link">2.3、分类3：内连接 vs 外连接</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/3a50a8/#_3-sql99语法实现多表查询" class="sidebar-link">3. SQL99语法实现多表查询</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3a50a8/#_3-1-基本语法" class="sidebar-link">3.1 基本语法</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3a50a8/#_3-2-内连接-inner-join-的实现" class="sidebar-link">3.2 内连接(INNER JOIN)的实现</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3a50a8/#_3-3-外连接-outer-join-的实现" class="sidebar-link">3.3 外连接(OUTER JOIN)的实现</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/3a50a8/#_4-union的使用" class="sidebar-link">4. UNION的使用</a></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/3a50a8/#_5-7种sql-joins的实现" class="sidebar-link">5. 7种SQL JOINS的实现</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3a50a8/#_5-7-1-代码实现" class="sidebar-link">5.7.1 代码实现</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3a50a8/#_5-7-2-语法格式小结" class="sidebar-link">5.7.2 语法格式小结</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/3a50a8/#_6-sql99语法新特性" class="sidebar-link">6. SQL99语法新特性</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3a50a8/#_6-1-自然连接" class="sidebar-link">6.1 自然连接</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/3a50a8/#_6-2-using连接" class="sidebar-link">6.2 USING连接</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/3a50a8/#_7-章节小结" class="sidebar-link">7. 章节小结</a></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/3a50a8/#附录-常用的-sql-标准有哪些" class="sidebar-link">附录：常用的 SQL 标准有哪些</a></li></ul></li><li><a href="/noteslibrary/pages/8de5b4/" class="sidebar-link">单行函数</a></li><li><a href="/noteslibrary/pages/2dfe6d/" class="sidebar-link">聚合函数</a></li><li><a href="/noteslibrary/pages/3ea5ac/" class="sidebar-link">子查询</a></li><li><a href="/noteslibrary/pages/70a90b/" class="sidebar-link">创建和管理表</a></li><li><a href="/noteslibrary/pages/a20c5b/" class="sidebar-link">数据处理之增删改</a></li><li><a href="/noteslibrary/pages/9515c0/" class="sidebar-link">MySQL数据类型精讲</a></li><li><a href="/noteslibrary/pages/98e681/" class="sidebar-link">约束</a></li><li><a href="/noteslibrary/pages/4535e0/" class="sidebar-link">视图</a></li><li><a href="/noteslibrary/pages/e5e604/" class="sidebar-link">存储过程与函数</a></li><li><a href="/noteslibrary/pages/f68b46/" class="sidebar-link">变量与流程控制与游标</a></li><li><a href="/noteslibrary/pages/fe941e/" class="sidebar-link">触发器</a></li><li><a href="/noteslibrary/pages/efad7b/" class="sidebar-link">MySQL8其它新特性</a></li></ul></section></li></ul> </aside> <div><main class="page"><div class="theme-vdoing-wrapper "><div class="articleInfo-wrap" data-v-06225672><div class="articleInfo" data-v-06225672><ul class="breadcrumbs" data-v-06225672><li data-v-06225672><a href="/noteslibrary/" title="首页" class="iconfont icon-home router-link-active" data-v-06225672></a></li> <li data-v-06225672><a href="/noteslibrary/database/#数据库" data-v-06225672>数据库</a></li><li data-v-06225672><a href="/noteslibrary/database/#MySQL笔记" data-v-06225672>MySQL笔记</a></li></ul> <div class="info" data-v-06225672><!----> <div title="创建时间" class="date iconfont icon-riqi" data-v-06225672><a href="javascript:;" data-v-06225672>2023-04-05</a></div> <!----></div></div></div> <!----> <div class="content-wrapper"><div class="right-menu-wrapper"><div class="right-menu-margin"><div class="right-menu-title">目录</div> <div class="right-menu-content"></div></div></div> <h1><img src="">多表查询<!----></h1> <!----> <div class="theme-vdoing-content content__default"><h1 id="多表查询"><a href="#多表查询" class="header-anchor">#</a> 多表查询</h1> <blockquote><p>多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。</p> <p>前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。</p></blockquote> <h2 id="_1-一个案例引发的多表连接"><a href="#_1-一个案例引发的多表连接" class="header-anchor">#</a> 1. 一个案例引发的多表连接</h2> <h3 id="_1-1-案例说明"><a href="#_1-1-案例说明" class="header-anchor">#</a> 1.1 案例说明</h3> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840612.png" alt="image-20220214163801849"></p> <p>从多个表中获取数据：</p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840613.png" alt="image-20220214163826105"></p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840614.png" alt="image-20220214163859863"></p> <p>查询结果：</p> <img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840615.png" alt="image-20220214164023906"> <p><strong>分析错误情况：</strong></p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT COUNT(employee_id) FROM employees;
#输出107行

SELECT COUNT(department_id)FROM departments;
#输出27行

SELECT 107*27 FROM dual;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p>我们把上述多表查询中出现的问题称为：笛卡尔积的错误。</p> <h3 id="_1-2-笛卡尔积-或交叉连接-的理解"><a href="#_1-2-笛卡尔积-或交叉连接-的理解" class="header-anchor">#</a> 1.2 笛卡尔积（或交叉连接）的理解</h3> <img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840616.png" alt="image-20220214164149875"> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>#查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h3 id="_1-3-案例分析与问题解决"><a href="#_1-3-案例分析与问题解决" class="header-anchor">#</a> 1.3 案例分析与问题解决</h3> <ul><li><p><strong>笛卡尔积的错误会在下面条件下产生：</strong></p> <ul><li>省略多个表的连接条件（或关联条件）</li> <li>连接条件（或关联条件）无效</li> <li>所有表中的所有行互相连接</li></ul></li> <li><p>为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。</p></li> <li><p>加入连接条件后，查询语法：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;  #连接条件
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><ul><li><strong>在 WHERE子句中写入连接条件。</strong></li></ul></li> <li><p>正确写法：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div></li> <li><p><strong>在表中有相同列时，在列名之前加上表名前缀。</strong></p></li></ul> <h2 id="_2-多表查询分类讲解"><a href="#_2-多表查询分类讲解" class="header-anchor">#</a> 2. 多表查询分类讲解</h2> <h3 id="_2-1、分类1-等值连接vs非等值连接"><a href="#_2-1、分类1-等值连接vs非等值连接" class="header-anchor">#</a> 2.1、分类1：等值连接VS非等值连接</h3> <h4 id="_2-1-1、等值连接"><a href="#_2-1-1、等值连接" class="header-anchor">#</a> 2.1.1、等值连接</h4> <img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840617.png" alt="image-20220214164434634"> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT employees.employee_id, employees.last_name,
   employees.department_id, departments.department_id,
   departments.location_id
FROM  employees, departments
WHERE employees.department_id = departments.department_id;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840618.png" alt="image-20220214164538267"> <img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840619.png" alt="image-20220214164827037"> <h4 id="_2-1-2、非等值连接"><a href="#_2-1-2、非等值连接" class="header-anchor">#</a> 2.1.2、非等值连接</h4> <img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840620.png" alt="image-20220214165108077"> <h3 id="_2-2、分类2-自连接-vs-非自连接"><a href="#_2-2、分类2-自连接-vs-非自连接" class="header-anchor">#</a> 2.2、分类2：自连接 vs 非自连接</h3> <img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840621.png" alt="image-20220214165355806"> <h3 id="_2-3、分类3-内连接-vs-外连接"><a href="#_2-3、分类3-内连接-vs-外连接" class="header-anchor">#</a> 2.3、分类3：内连接 vs 外连接</h3> <img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840622.png" alt="image-20220214165448624"> <ul><li>SQL92：使用(+)创建连接</li></ul> <img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840623.png" alt="image-20220214165555027"> <h2 id="_3-sql99语法实现多表查询"><a href="#_3-sql99语法实现多表查询" class="header-anchor">#</a> 3. SQL99语法实现多表查询</h2> <h3 id="_3-1-基本语法"><a href="#_3-1-基本语法" class="header-anchor">#</a> 3.1 基本语法</h3> <ul><li><p>使用JOIN...ON子句创建连接的语法结构：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT table1.column, table2.column,table3.column
FROM table1
	JOIN table2 ON table1 和 table2 的连接条件
		JOIN table3 ON table2 和 table3 的连接条件
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>它的嵌套逻辑类似我们使用的 FOR 循环：</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token keyword">for</span> t1 in table1<span class="token operator">:</span>
  <span class="token keyword">for</span> t2 in table2<span class="token operator">:</span>
   <span class="token keyword">if</span> condition1<span class="token operator">:</span>
     <span class="token keyword">for</span> t3 in table3<span class="token operator">:</span>
       <span class="token keyword">if</span> condition2<span class="token operator">:</span>
        output t1 <span class="token operator">+</span> t2 <span class="token operator">+</span> t3
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强，即使再多的表进行连接也都清晰可见。如果你采用 SQL92，可读性就会大打折扣。</p></li> <li><p>语法说明：</p> <ul><li>可以使用 ON 子句指定额外的连接条件。</li> <li>这个连接条件是与其它条件分开的。</li> <li>ON 子句使语句具有更高的易读性。</li> <li>关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接</li></ul></li></ul> <h3 id="_3-2-内连接-inner-join-的实现"><a href="#_3-2-内连接-inner-join-的实现" class="header-anchor">#</a> 3.2 内连接(INNER JOIN)的实现</h3> <ul><li><p>语法</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>题目1：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT e.employee_id, e.last_name, e.department_id,
   d.department_id, d.location_id
FROM  employees e JOIN departments d
ON   (e.department_id = d.department_id);
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840624.png" alt="image-20220214165920045"></p> <p>题目2：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT employee_id, city, department_name
FROM  employees e
JOIN  departments d
ON   d.department_id = e.department_id
JOIN  locations l
ON   d.location_id = l.location_id;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840625.png" alt="image-20220214165951827"></p></li></ul> <h3 id="_3-3-外连接-outer-join-的实现"><a href="#_3-3-外连接-outer-join-的实现" class="header-anchor">#</a> 3.3 外连接(OUTER JOIN)的实现</h3> <h4 id="_3-3-1-左外连接-left-outer-join"><a href="#_3-3-1-左外连接-left-outer-join" class="header-anchor">#</a> 3.3.1 左外连接(LEFT OUTER JOIN)</h4> <ul><li><p>语法：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div></li> <li><p>举例：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT e.last_name, e.department_id, d.department_name
FROM  employees e
LEFT OUTER JOIN departments d
ON  (e.department_id = d.department_id) ;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840626.png" alt="image-20220214170106064"></p></li></ul> <h4 id="_3-3-2-右外连接-right-outer-join"><a href="#_3-3-2-右外连接-right-outer-join" class="header-anchor">#</a> 3.3.2 右外连接(RIGHT OUTER JOIN)</h4> <ul><li><p>语法：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div></li> <li><p>举例：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT e.last_name, e.department_id, d.department_name
FROM  employees e
RIGHT OUTER JOIN departments d
ON  (e.department_id = d.department_id) ;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840627.png" alt="image-20220214170205747"></p> <blockquote><p><strong>需要注意的是，LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在，只能用 (+) 表示。</strong></p></blockquote></li></ul> <h4 id="_3-3-3-满外连接-full-outer-join"><a href="#_3-3-3-满外连接-full-outer-join" class="header-anchor">#</a> 3.3.3 满外连接(FULL OUTER JOIN)</h4> <ul><li><p>满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。</p></li> <li><p>SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。</p></li> <li><p>需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN <strong>UNION</strong> RIGHT join代替。</p> <blockquote><p><strong>满外连接：mysql不支持FULL OUTER JOIN</strong></p></blockquote></li></ul> <h2 id="_4-union的使用"><a href="#_4-union的使用" class="header-anchor">#</a> 4. UNION的使用</h2> <p>合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。<strong>合并时，两个表对应的列数和数据类型必须相同，并且相互对应</strong>。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。</p> <p>语法格式：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><strong>UNION操作符</strong></p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840628.png" alt="image-20220214170411985"></p> <blockquote><p>UNION 操作符返回两个查询的结果集的并集，去除重复记录。</p></blockquote> <p><strong>UNION ALL操作符</strong></p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840629.png" alt="image-20220214170438074"></p> <blockquote><p>UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。</p></blockquote> <blockquote><p>注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。</p></blockquote> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840630.png" alt="image-20220214170512088"></p> <h2 id="_5-7种sql-joins的实现"><a href="#_5-7种sql-joins的实现" class="header-anchor">#</a> 5. 7种SQL JOINS的实现</h2> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840631.png" alt="image-20220214170539035"></p> <h3 id="_5-7-1-代码实现"><a href="#_5-7-1-代码实现" class="header-anchor">#</a> 5.7.1 代码实现</h3> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>#中图：内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><div class="language-mysql line-numbers-mode"><pre class="language-text"><code>#左上图：左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><div class="language-mysql line-numbers-mode"><pre class="language-text"><code>#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><div class="language-mysql line-numbers-mode"><pre class="language-text"><code>#左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><div class="language-mysql line-numbers-mode"><pre class="language-text"><code>#右中图：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><div class="language-mysql line-numbers-mode"><pre class="language-text"><code>#左下图：满外连接
# 左中图 + 右上图 A∪B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL  #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><div class="language-mysql line-numbers-mode"><pre class="language-text"><code>#右下图
#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br></div></div><h3 id="_5-7-2-语法格式小结"><a href="#_5-7-2-语法格式小结" class="header-anchor">#</a> 5.7.2 语法格式小结</h3> <ul><li>左中图</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#实现A - A∩B</span>
<span class="token keyword">select</span> 字段列表
<span class="token keyword">from</span> A表 <span class="token keyword">left</span> <span class="token keyword">join</span> B表
<span class="token keyword">on</span> 关联条件
<span class="token keyword">where</span> 从表关联字段 <span class="token operator">is</span> <span class="token boolean">null</span> <span class="token operator">and</span> 等其他子句<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><ul><li>右中图</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#实现B - A∩B</span>
<span class="token keyword">select</span> 字段列表
<span class="token keyword">from</span> A表 <span class="token keyword">right</span> <span class="token keyword">join</span> B表
<span class="token keyword">on</span> 关联条件
<span class="token keyword">where</span> 从表关联字段 <span class="token operator">is</span> <span class="token boolean">null</span> <span class="token operator">and</span> 等其他子句<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><ul><li>左下图</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#实现查询结果是A∪B</span>
<span class="token comment">#用左外的A，union 右外的B</span>
<span class="token keyword">select</span> 字段列表
<span class="token keyword">from</span> A表 <span class="token keyword">left</span> <span class="token keyword">join</span> B表
<span class="token keyword">on</span> 关联条件
<span class="token keyword">where</span> 等其他子句

<span class="token keyword">union</span>

<span class="token keyword">select</span> 字段列表
<span class="token keyword">from</span> A表 <span class="token keyword">right</span> <span class="token keyword">join</span> B表
<span class="token keyword">on</span> 关联条件
<span class="token keyword">where</span> 等其他子句<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div><ul><li>右下图</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#实现A∪B - A∩B 或  (A - A∩B) ∪ （B - A∩B）</span>
<span class="token comment">#使用左外的 (A - A∩B) union 右外的（B - A∩B）</span>
<span class="token keyword">select</span> 字段列表
<span class="token keyword">from</span> A表 <span class="token keyword">left</span> <span class="token keyword">join</span> B表
<span class="token keyword">on</span> 关联条件
<span class="token keyword">where</span> 从表关联字段 <span class="token operator">is</span> <span class="token boolean">null</span> <span class="token operator">and</span> 等其他子句

<span class="token keyword">union</span>

<span class="token keyword">select</span> 字段列表
<span class="token keyword">from</span> A表 <span class="token keyword">right</span> <span class="token keyword">join</span> B表
<span class="token keyword">on</span> 关联条件
<span class="token keyword">where</span> 从表关联字段 <span class="token operator">is</span> <span class="token boolean">null</span> <span class="token operator">and</span> 等其他子句
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div><h2 id="_6-sql99语法新特性"><a href="#_6-sql99语法新特性" class="header-anchor">#</a> 6. SQL99语法新特性</h2> <h3 id="_6-1-自然连接"><a href="#_6-1-自然连接" class="header-anchor">#</a> 6.1 自然连接</h3> <p>SQL99 在 SQL92 的基础上提供了一些特殊语法，比如  <code>NATURAL JOIN</code> 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 <code>所有相同的字段</code> ，然后进行 <code>等值连接</code> 。
在SQL92标准中：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>在 SQL99 中可以写成：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h3 id="_6-2-using连接"><a href="#_6-2-using连接" class="header-anchor">#</a> 6.2 USING连接</h3> <p>当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 <code>同名字段</code> 进行等值连接。但是只能配合JOIN一起使用。比如：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING的括号 () 中填入要指定的同名字段。同时使用  <code>JOIN...USING</code>可以简化 JOIN ON 的等值连接。它与下面的 SQL 查询结果是相同的：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h2 id="_7-章节小结"><a href="#_7-章节小结" class="header-anchor">#</a> 7. 章节小结</h2> <p>表连接的约束条件可以有三种方式：WHERE, ON, USING</p> <ul><li><p>WHERE：适用于所有关联查询</p></li> <li><p><code>ON</code>：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起
写，但分开写可读性更好。</p></li> <li><p>USING：只能和JOIN一起使用，而且要求<code>两个</code>关联字段在关联表中名称一致，而且只能表示关联字段值相等</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>#关联条件
#把关联条件写在where后面
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;

#把关联条件写在on后面，只能和JOIN一起使用
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees CROSS JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name 
FROM employees JOIN departments
ON employees.department_id = departments.department_id;

#把关联字段写在using()中，只能和JOIN一起使用
#而且两个表中的关联字段必须名称相同，而且只能表示=
#查询员工姓名与基本工资
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);

#n张表关联，需要n-1个关联条件
#查询员工姓名，基本工资，部门名称
SELECT last_name,job_title,department_name FROM employees,departments,jobs
WHERE employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;
SELECT last_name,job_title,department_name
FROM employees INNER JOIN departments INNER JOIN jobs
ON employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br></div></div><p><strong>注意：</strong>
我们要 <code>控制连接表的数量</code> 。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。</p> <blockquote><p>【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能。
来源：阿里巴巴《Java开发手册》</p></blockquote></li></ul> <h2 id="附录-常用的-sql-标准有哪些"><a href="#附录-常用的-sql-标准有哪些" class="header-anchor">#</a> 附录：常用的 SQL 标准有哪些</h2> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840632.png" alt=""></p></div></div> <!----> <div class="page-edit"><div class="edit-link"><a href="https://github.com/lcfqzd/vuepress-theme-vdoing/edit/master/docs/04.数据库/01.MySQL笔记/06.多表查询.md" target="_blank" rel="noopener noreferrer">编辑</a> <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></div> <div class="tags"><a href="/noteslibrary/tags/?tag=MySQL" title="标签">#MySQL</a></div> <!----></div> <div class="page-nav-wapper"><div class="page-nav-centre-wrap"><a href="/noteslibrary/pages/6fef5b/" class="page-nav-centre page-nav-centre-prev"><div class="tooltip">排序与分页</div></a> <a href="/noteslibrary/pages/8de5b4/" class="page-nav-centre page-nav-centre-next"><div class="tooltip">单行函数</div></a></div> <div class="page-nav"><p class="inner"><span class="prev">
        ←
        <a href="/noteslibrary/pages/6fef5b/" class="prev">排序与分页</a></span> <span class="next"><a href="/noteslibrary/pages/8de5b4/">单行函数</a>→
      </span></p></div></div></div> <!----></main></div> <div class="footer"><!----> 
  Theme by
  <a href="https://github.com/xugaoyi/vuepress-theme-vdoing" target="_blank" title="本站主题">Vdoing</a> 
    | Copyright © 2018-2023
    <span>LCFQZD | MIT License</span></div> <div class="buttons"><div title="返回顶部" class="button blur go-to-top iconfont icon-fanhuidingbu" style="display:none;"></div> <div title="去评论" class="button blur go-to-comment iconfont icon-pinglun" style="display:none;"></div> <div title="主题模式" class="button blur theme-mode-but iconfont icon-zhuti"><ul class="select-box" style="display:none;"><li class="iconfont icon-zidong">
          跟随系统
        </li><li class="iconfont icon-rijianmoshi">
          浅色模式
        </li><li class="iconfont icon-yejianmoshi">
          深色模式
        </li><li class="iconfont icon-yuedu">
          阅读模式
        </li></ul></div></div> <!----> <!----> <!----></div><div class="global-ui"></div></div>
    <script src="/noteslibrary/assets/js/app.dc0c3d24.js" defer></script><script src="/noteslibrary/assets/js/2.a664539c.js" defer></script><script src="/noteslibrary/assets/js/93.bbc81ca6.js" defer></script>
  </body>
</html>
